Microsoft ACCESS Example

After loading Microsoft Access select NEW DATABASE from the 
FILE MENU.  Name the new database ZIPCODES.MDB and choose 
OK.

Next select IMPORT from the FILE MENU.  For the database type 
choose dBase III.  Select ZIPCODES.DBF for the file to import and 
press the IMPORT button.  It will take a few minutes to 
import the file.  When the import is complete you should see a 
message telling you that the import was successful. Close the IMPORT 
window.

Press the DESIGN button to change the design of the ZIPCODES 
table.  Highlight the ZIPCODE Field by pressing the grayed area 
directly to the left of the ZIPCODE field.  Press the KEY icon to make 
the ZIPCODE field the primary key.  A picture of a key should appear 
next to the ZIPCODE field name. Select SAVE from the FILE MENU 
to save the changes you made and wait a few minutes for the index to 
be created.  Now close the DESIGN window.

Now press the NEW button to create a new table.  Enter the following 
fields into this table:
	
Field Name      Data Type       Field Size

First Name        Text          30 characters
Last Name         Text          30 characters
Address           Text          30 characters
Zip Code          Text           5 characters
City              Text          30 characters
State             Text           2 characters
Phone             Text          10 characters

Select SAVE from the FILE MENU and name the table DATA TABLE.  When 
you are asked for a primary key just select NO.  Close the NEW TABLE 
window.

Now press the FORM button from the DATABASE WINDOW and then press the 
NEW button to create a new form.  Select DATA TABLE as the table to 
build the FORM from then press the FORM WIZARDS button.

Choose the SINGLE-COLUMN Wizard.  When you are asked which fields you want 
to add to your form press the >> button to move all fields into the new 
form and then press the NEXT button.  Next click on EMBOSSED and then NEXT.

Now press the design button to open the form in design mode.  Double click
on the Zip Code field to reveal the properties.  In the AFTER UPDATE 
property enter =GetCityState().

Save the form with the name DATA FORM and close the form.  

Press the MODULE button and then the NEW button to create a new module.
Type in the following function:

Function GetCityState()
	Forms![DATA FORM].City = DLOOKUP("[City]" , "ZIPCODES", 
		"[ZIPCODE] = Forms![DATA FORM].[Zip Code]")
	Forms![DATA FORM].State= DLOOKUP("[State]", "ZIPCODES", 
		"[ZIPCODE] = Forms![DATA FORM].[Zip Code]")
End Function

Save the MODULE as GetCityState and close it.

Now press the FORM Button, select the DATA FORM, and press the OPEN
button to open the form and try entering a few records.  The city and
state will be entered automatically when you enter a valid ZIP Code.

Using this method, the city and state are looked up whenever the ZIP Code 
is changed.  

Try adding a couple of records.  When you get to the ZIP CODE field try 
punching in your own ZIP Code and let ACCESS look up the city and state 
for you.

If you wish, you can prevent editing in the City and State fields and 
skip over them entirely by changing the ENABLED property of the City and 
State controls in the LOOKUP FORM to NO.

If you have any comments, suggestions, or questions please contact me at:

HELP Software
PO BOX 1423
Raymore, MO 64083
(816) 331-5809
CompuServe 73720,2530